#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_db_access_driver_PyMySQL.py
# author:张世航
# datetime:2024/12/3 23:51
# software: PyCharm
"""
2  练习使用数据库库访问驱动PyMySQL
   设计一个留言版的表（留言ID，留言内容，留言人，留言审核状态，留言时间）（表名，和字段名自己设计成英文：注意，不要用中文，用中文的直接0分）；
   使用PyMySQL 驱动模块，实现对这个表的增加，删除，修改，查询；数据库操作需要加入异常处理逻辑；

"""
# import module your need


import pymysql

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'user': 'root',  # 替换为你的数据库用户名
    'password': '158158',  # 替换为你的数据库密码
    'database': 'dbtest10.2',  # 替换为你的数据库名称
    'charset': 'utf8mb4'
}

def connect_db():
    """连接数据库"""
    try:
        connection = pymysql.connect(**db_config)
        return connection
    except pymysql.MySQLError as e:
        print(f"Error connecting to database: {e}")
        return None

def add_message(content, author):
    """添加留言"""
    connection = connect_db()
    if connection:
        try:
            with connection.cursor() as cursor:
                sql = "INSERT INTO message_board (content, author) VALUES (%s, %s)"
                cursor.execute(sql, (content, author))
                connection.commit()
                print("Message added successfully.")
        except pymysql.MySQLError as e:
            print(f"Error adding message: {e}")
        finally:
            connection.close()

def delete_message(message_id):
    """删除留言"""
    connection = connect_db()
    if connection:
        try:
            with connection.cursor() as cursor:
                sql = "DELETE FROM message_board WHERE message_id = %s"
                cursor.execute(sql, (message_id,))
                connection.commit()
                print("Message deleted successfully.")
        except pymysql.MySQLError as e:
            print(f"Error deleting message: {e}")
        finally:
            connection.close()

def update_message(message_id, content, status):
    """修改留言"""
    connection = connect_db()
    if connection:
        try:
            with connection.cursor() as cursor:
                sql = "UPDATE message_board SET content = %s, status = %s WHERE message_id = %s"
                cursor.execute(sql, (content, status, message_id))
                connection.commit()
                print("Message updated successfully.")
        except pymysql.MySQLError as e:
            print(f"Error updating message: {e}")
        finally:
            connection.close()

def query_messages():
    """查询所有留言"""
    connection = connect_db()
    if connection:
        try:
            with connection.cursor() as cursor:
                sql = "SELECT * FROM message_board"
                cursor.execute(sql)
                results = cursor.fetchall()
                for row in results:
                    print(row)
        except pymysql.MySQLError as e:
            print(f"Error querying messages: {e}")
        finally:
            connection.close()

def display_menu():
    """显示菜单"""
    print("\n--- Message Board Menu ---")
    print("1. Add Message")
    print("2. Delete Message")
    print("3. Update Message")
    print("4. Query Messages")
    print("5. Exit")

def main():
    while True:
        display_menu()
        choice = input("Please choose an option (1-5): ")

        if choice == '1':
            content = input("Enter your message content: ")
            author = input("Enter your name: ")
            add_message(content, author)

        elif choice == '2':
            message_id = input("Enter the message ID to delete: ")
            delete_message(message_id)

        elif choice == '3':
            message_id = input("Enter the message ID to update: ")
            content = input("Enter the new message content: ")
            status = input("Enter the new status (0-待审核, 1-通过, 2-驳回): ")
            update_message(message_id, content, status)

        elif choice == '4':
            print("All messages:")
            query_messages()

        elif choice == '5':
            print("Exiting the program.")
            break

        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    main()